package com.ws.datasync;

import com.ws.datasync.common.util.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.*;

/**
 * function: 定时轮询同步表
 *
 * @author pihao
 * @version 1.0
 * @since 2021-03-05 09:36
 */
@Component
@Slf4j
public class AsyncTask{
    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate primaryTemplate; //目的连接
    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    private JdbcTemplate secondTemplate;  //源头连接
    @Value("${async.tables}")
    private String tableName[]; //需要同步的表
    @Value("${spring.datasource.primary.name}")
    private String primaryDbName; //目的数据库的名字
    @Value("${spring.datasource.primary.type}")
    private String primaryDBType; //目的数据库的类型
    @Value("${spring.datasource.secondary.type}")
    private String secondDbType; //源数据库的类型


//    @Scheduled(cron = "0 */5 * * * ?")
    @Scheduled(fixedDelay = 2000)
    public void start(){
//        System.out.println("执行啦！！！");
//        System.out.println(primaryTemplate);
//        System.out.println(primaryDbName);
//        System.out.println(primaryDBType);
//        System.out.println(secondTemplate);
//        System.out.println(secondDbType);
//        System.out.println(Arrays.toString(tableName));

        for (int i = 0;i<tableName.length;i++){
            String table = tableName[i];
            log.info("==================================同步表进度：[{}/{}],正在同步的表：{}==================================",i+1,tableName.length,table);
            try {
                //检查目的数据库中是否存在该表
                int count = checkTable(table,primaryDBType,primaryDbName);
                if(count < 0){
                    throw new RuntimeException("目的数据库中不存在该表,无法同步,请先创建该表: " + table);
                }else{
                    //存在该表的话,获取两张表中的所有数据
                    String sql = "select * from "+table;
                    List<Map<String, Object>> secondMap = secondTemplate.queryForList(sql);
                    List<Map<String, Object>> primaryMap = primaryTemplate.queryForList(sql);
                    int updateCount = 0;
                    int insertCount = 0;
                    log.info("当前表:{},共查出{}条记录,开始同步",table,secondMap.size());
                    for (int j=0;j<secondMap.size();j++){
                        Map<String, Object> map = secondMap.get(j);
                        //找到当前记录主键的值

                        String pk = getTablePk(secondDbType, table);
                        if(StringUtils.isEmpty(pk)){
                            throw new RuntimeException("当前表："+table+",没有找到主键" +",请核对!");
                        }
                        String pkValue = String.valueOf(map.get(pk));

                        //根据主键查找目的表,是否能找到记录
                        String queryByPkSql = "select * from "+table+" where "+ pk +" = '"+pkValue+ "'";
                        //源数据库该表的所有字段
                        List<Map<String, Object>> fieldList = CommonUtil.getFildCountList(table, primaryDBType, secondDbType, secondTemplate);
                        List<Map<String, Object>> pkValueMap = primaryTemplate.queryForList(queryByPkSql);

                        if(pkValueMap.size() > 0){
                            //目的数据库的表中存在该记录,视情况更新或者放过
                            /**
                             * TODO 对比的时候这里有坑，oracle查询出来的map里面的键都是大写的
                             */
                            Map<String, Object> map1 = pkValueMap.get(0);
                            boolean flag= checkTwoMapsSame(map,map1);

                            if(flag){
                                //说明这两条记录一样，不更新
                                log.info("当前表:{},总记录数:{},当前进度：[{}/{}],主键:{}={},查到了相同的记录不更新",
                                                        table,secondMap.size(),j+1,secondMap.size(), pk,pkValue);
                            }else{
                                //不一样，发生了变动，更新
                                int num = updateInAddToDataByPrepareStatement(fieldList, table, pkValue, map,pk);
                                updateCount += num;
                                log.info("当前表:{},总记录数:{},当前进度：[{}/{}],主键:{}={},发现记录发生了变化已更新成功",
                                        table,secondMap.size(),j+1,secondMap.size(), pk,pkValue);
                            }

                        }else{
                            //目的数据库的表中不存在该记录,则插入
                            int num = insertInAddToData(fieldList, table, pkValue,pk);
                            insertCount+=num;
                            log.info("当前表:{},总记录数:{},当前进度：[{}/{}],主键:{}={},发现记录不存在，已新增",
                                    table,secondMap.size(),j+1,secondMap.size(),pk,pkValue);

                        }
                    }
                    log.info("==================================当前表：{} 同步完成,新增数:{},更新数:{}==================================",table,insertCount,updateCount);
                }
            }catch (Exception e){
                log.error("当前表同步失败,表名:{},原因:{}",table,e.getMessage());
            }

        }

        try {
            Thread.sleep(100);
        }catch (Exception e){

        }


    }

    /**
     * 判断两个map里面的值是否一样
     * @param map
     * @param map1
     * @return
     */
    private boolean checkTwoMapsSame(Map<String, Object> map, Map<String, Object> map1) {
        Set<Map.Entry<String, Object>> entries = map.entrySet();
        Iterator<Map.Entry<String, Object>> iterator = entries.iterator();
        while (iterator.hasNext()){
            Map.Entry<String, Object> next = iterator.next();
            String key = next.getKey();
            Object value = next.getValue();
            Set<String> keys = map1.keySet();
            for (String k:keys) {
                if(key.equalsIgnoreCase(k)){
                    Object o = map1.get(k);
                    String valueStr = String.valueOf(value);
                    String oStr = String.valueOf(o);
                    if(valueStr.hashCode() != oStr.hashCode()){
                        return false;
                    }
                }
            }

        }
        return true;
    }


    /**
     * 检验目的数据库中是否存在该表,没有效果
     * @param tableName
     * @param primaryDBType
     * @return
     */
    private int checkTable(String tableName,String primaryDBType,String primaryName) {
        String checkTableSql = "";
        if ("oracle".equalsIgnoreCase(primaryDBType)) {
            checkTableSql = "SELECT COUNT(*) AS C FROM USER_TABLES WHERE TABLE_NAME = '" + tableName.toUpperCase() + "'";
        } else if ("mysql".equalsIgnoreCase(primaryDBType)) {
            checkTableSql = "select count(*) as C from (select table_name from information_schema.tables where table_schema='" + primaryName + "') t where t.table_name = '" + tableName + "'";
        } else {

        }
        List<Map<String, Object>> listMap = primaryTemplate.queryForList(checkTableSql);
        String c = listMap.get(0).get("C").toString();
        Integer count = Integer.valueOf(c);
        return count;
    }

    /**
     * 获取源数据库表的主键
     * @return
     */
    public String getTablePk(String dbType,String table){
        String getPkSql = "";
        if(dbType.equals("mysql")){
            getPkSql = "SELECT COLUMN_NAME\n" +
                    "  FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` \n" +
                    " WHERE table_name='"+table+"' \n" +
                    " AND constraint_name='PRIMARY'";
        }else if(dbType.equals("oracle")){
            getPkSql = "SELECT\n" +
                    "\tcu.COLUMN_NAME \n" +
                    "FROM\n" +
                    "\tuser_cons_columns cu,\n" +
                    "\tuser_constraints au \n" +
                    "WHERE\n" +
                    "\tcu.constraint_name = au.constraint_name \n" +
                    "\tAND au.constraint_type = 'P' \n" +
                    "\tAND au.table_name = "+table.toUpperCase();
        }else{

        }

        List<Map<String, Object>> maps = secondTemplate.queryForList(getPkSql);
        String column_name = (String) maps.get(0).get("COLUMN_NAME");
        return column_name;
    }


    /**
     *
     * @param fieldCountList
     * @param tableName
     * @param pkValue
     * @param map
     * @return
     */
    private int updateInAddToData(List<Map<String, Object>> fieldCountList, String tableName, String pkValue, Map<String, Object> map,String pk) {
        String updateSetStr = "";
        for (Map<String, Object> countMap : fieldCountList) {
            for (String columnKey : countMap.keySet()) {
                String column = countMap.get(columnKey).toString();
                if (!column.equalsIgnoreCase(pk)) {
                    //这里有坑，jdbcTemplate不能更新为Null值的
                    Object columnVal = map.get(column);
                    if(columnVal != null){
                        updateSetStr += column + "= '" + map.get(column) + "',";
                    }
                }
            }
        }

        updateSetStr = updateSetStr.substring(0, updateSetStr.lastIndexOf(","));
        String updateByIdSql = "update " + tableName + " set " + updateSetStr + " where "+pk+" = '" + pkValue + "'";
        int count = primaryTemplate.update(updateByIdSql);
        return count;
    }

    /**
     * 以预编译的方式执行sql,如果直接以上面的方法执行，Date类型更新时会有异常。Timestamp
     * @param fieldCountList
     * @param tableName
     * @param pkValue
     * @param map
     * @param pk
     * @return
     */
    private int updateInAddToDataByPrepareStatement(List<Map<String, Object>> fieldCountList, String tableName, String pkValue, Map<String, Object> map,String pk) {
        String updateSetStrTemp = "";
        ArrayList<Object> tempList = new ArrayList<>();
        for (Map<String, Object> countMap : fieldCountList) {
            for (String columnKey : countMap.keySet()) {
                String column = countMap.get(columnKey).toString();
                if (!column.equalsIgnoreCase(pk)) {
                    //这里有坑，jdbcTemplate不能更新为Null值的
                    Object columnVal = map.get(column);
                    if(columnVal != null){
                        //拼接key
                        updateSetStrTemp += column + "= ?,";
                        //拼接值
                        tempList.add(map.get(column));
                    }
                }
            }
        }

        Object[] obj = tempList.toArray();
        updateSetStrTemp = updateSetStrTemp.substring(0, updateSetStrTemp.lastIndexOf(","));
        String updateByIdSql = "update " + tableName + " set " + updateSetStrTemp + " where "+pk+" = '" + pkValue + "'";
        int count = primaryTemplate.update(updateByIdSql,obj);
        return count;
    }



    /**
     * 插入数据
     * @param fieldList
     * @param tableName
     * @param pkValue
     * @return
     */
    private int insertInAddToData(List<Map<String, Object>> fieldList, String tableName, String pkValue,String pk) {
        String getDataById = "SELECT * FROM " + tableName + " WHERE "+pk+" ='" + pkValue + "'";
        List<Map<String, Object>> list = secondTemplate.queryForList(getDataById);
        int size = fieldList.size();
        String temp = "";
        for (int j = 0; j < size; j++) {
            temp += "?,";
        }
        temp = temp.substring(0, temp.lastIndexOf(","));
        String insertSql = "INSERT INTO " + tableName + " VALUES(" + temp + ")";
        int count = 0;
        for (Map<String, Object> tempmap : list) {
            ArrayList<Object> tempList = new ArrayList<>();
            for (Object value : tempmap.values()) {
                tempList.add(value);
            }
            Object[] obj = tempList.toArray();
            int update = primaryTemplate.update(insertSql, obj);
            count += update;
        }
        return count;
    }
}
